﻿CREATE TABLE [Lunch].[Reservations] (
    [ReservationId]    INT          IDENTITY (1, 1) NOT NULL,
    [GuestId]          INT          NOT NULL,
    [EventId]          INT          NOT NULL,
    [CreatedDateTime]  DATETIME     CONSTRAINT [DF_Reservations_CreatedDateTime] DEFAULT (getdate()) NOT NULL,
    [CreatedUserId]    VARCHAR (50) CONSTRAINT [DF_Reservations_CreatedUserId] DEFAULT (user_name()) NOT NULL,
    [ModifiedDateTime] DATETIME     CONSTRAINT [DF_Reservations_ModifiedDateTime] DEFAULT (getdate()) NOT NULL,
    [ModifiedUserId]   VARCHAR (50) CONSTRAINT [DF_Reservations_ModifiedUserId] DEFAULT (user_name()) NOT NULL,
    CONSTRAINT [PK_Reservations] PRIMARY KEY CLUSTERED ([ReservationId] ASC),
    CONSTRAINT [FK_Reservations_Events] FOREIGN KEY ([EventId]) REFERENCES [Lunch].[Events] ([EventId]),
    CONSTRAINT [FK_Reservations_Guests] FOREIGN KEY ([GuestId]) REFERENCES [Lunch].[Guests] ([GuestId])
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Reservations]
    ON [Lunch].[Reservations]([EventId] ASC, [GuestId] ASC);

